Database Trace

If you can’t isolate the problem down to a single user, session, or program / module, then you will have to trace the entire database. This will require two short outages – one to start the trace, the other to stop it. You will need co-operation from the DBA (to conduct the outages) and the primary stakeholders of the system (whose business will be impacted by the outages).

Note that whilst tracing the entire database it will run much slower and chew up disk like crazy, so keep it as short as possible – half an hour or less is preferred.

The DBA will shut down the database and re-mount it with Trace switched on. From this time all sessions will be fully traced. At the end of the trial period, all sessions should be logged off, and the DBA will shut down the database and re-mount it with Trace switched off.

Now you will have a separate trace file for every session that was initiated in the trial period. You can convert them to human readable form in one of 3 ways:

• Run TK*Prof over each of them individually.
• Concatenate the lot and run TK*Prof once to generate a single output file.
• With later versions of Oracle you can use the trcsess utility (see the Oracle Performance Tuning Manual). This is a more organised approach to tracing that allows you to group traces by session, client, service, action, or module.

Using the first method, go straight to the bottom of the output file and check the session statistics. Ignore sessions with low Call, Elapsed, Disk, and Current figures. Those trace files with relatively high number in these columns are the ones you want to look at in more detail.